import pymssql
import config

server = '172.16.3.19'
# server = '192.168.66.100'
user = 'sa'
password = '*#zw8'
if (config.db == 1):
    database = 'TOP_ERP_WHUT'
if (config.db == 2):
    database = 'TOP_ERP_HLJU'

try:

    # 打开数据库连接（ip/数据库用户名/登录密码/数据库名）
    db = pymssql.connect(server=server, user=user, password=password, database=database)
    print("数据库连接成功")
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
except pymssql.Error as e:
    print("数据库连接失败：" + str(e))

'''查询视图时添加是否是单双周字段 IsSDWeek   2024.5.6   SPF'''


def init_course_task():
    task_course = []
    if (config.db == 1):
        sql = ''' select VB_Score_Manage.Sno, VB_Score_Manage.CourseID,VB_Score_Manage.coursename,VB_Score_Manage.Teacher,VB_Score_Manage.TutorName,VB_Score_Manage.Class,VB_SelectCourse_Base.GraduateID,WeekStart,WeekEnd,WeekHour,VB_Score_Manage.StudyHour 
            from VB_Score_Manage left Join VB_SelectCourse_Base on VB_SelectCourse_Base.Sno = VB_Score_Manage.Sno 
            where VB_Score_Manage.year='2021-2022' and VB_Score_Manage.term='1' and WeekStart is not null and WeekEnd is not null and
            VB_Score_Manage.courseid not in('01544003','01524003','01524002','02614002','02614003','00114002','01214002','00414003','00214002','00214003','00514002','00514003','00114002','00414003','01314003','00914001',
            '00914002','00914003','01214001','01314003','01414002','01414001','01414002','01314001','01214003','01414003','00314005','01014003','00314005','00314006','01014001','01014003','00714001',
            '00314006','01114003','01114003','01114002','01114003','02624002','02624001','02624003','00124002','00162001','07024003','07024002','07024001','01024006','01024005','00324007',
            '00324006','05524001','00724004','01124002','01124003','01524301','01424004','01424003','0124002','01424005','01424002','00744001','00744002','00144001','00144003','00344015','00944003',
            '01944319','02141106','01544002','02644008','00614002L','00314002','01324901','00114003','00344902','00344801','00344013','00344012','00344014','01614001','01614002','00614003','00614001','01711410',
             '01714006','00111001') and VB_Score_Manage.deptid='001' and VB_SelectCourse_Base.GraduateID is not null '''
    if (config.db == 2):
        # sql = ''' select VB_Score_Manage.Sno, VB_Score_Manage.CourseID,VB_Score_Manage.coursename,VB_Score_Manage.Teacher,VB_Score_Manage.TutorName,VB_Score_Manage.Class,VB_SelectCourse_Base.GraduateID,WeekStart,WeekEnd,WeekHour,VB_Score_Manage.StudyHour
        # from VB_Score_Manage left Join VB_SelectCourse_Base on VB_SelectCourse_Base.Sno = VB_Score_Manage.Sno
        # where VB_Score_Manage.year='2022-2023' and VB_Score_Manage.term='2' and WeekStart is not null and WeekEnd is not null and
        # VB_Score_Manage.courseid not in('01544003','01524003','01524002','02614002','02614003','00114002','01214002','00414003','00214002','00214003','00514002','00514003','00114002','00414003','01314003','00914001',
        # '00914002','00914003','01214001','01314003','01414002','01414001','01414002','01314001','01214003','01414003','00314005','01014003','00314005','00314006','01014001','01014003','00714001',
        # '00314006','01114003','01114003','01114002','01114003','02624002','02624001','02624003','00124002','00162001','07024003','07024002','07024001','01024006','01024005','00324007',
        # '00324006','05524001','00724004','01124002','01124003','01524301','01424004','01424003','0124002','01424005','01424002','00744001','00744002','00144001','00144003','00344015','00944003',
        # '01944319','02141106','01544002','02644008','00614002L','00314002','01324901','00114003','00344902','00344801','00344013','00344012','00344014','01614001','01614002','00614003','00614001','01711410',
        #  '01714006','00111001') and VB_Score_Manage.deptid in('001','002','004','005') and VB_SelectCourse_Base.GraduateID is not null '''
        sql = ''' select VB_Score_Manage.Sno, VB_Score_Manage.CourseID,VB_Score_Manage.coursename,VB_Score_Manage.Teacher,VB_Score_Manage.TutorName,VB_Score_Manage.Class,VB_SelectCourse_Base.GraduateID,WeekStart,WeekEnd,WeekHour,VB_Score_Manage.StudyHour,VB_Score_Manage.IsSDWeek 
        from VB_Score_Manage left Join VB_SelectCourse_Base on VB_SelectCourse_Base.Sno = VB_Score_Manage.Sno 
        where VB_Score_Manage.year='2022-2023' and VB_Score_Manage.term='2' and WeekStart is not null and WeekEnd is not null and
        VB_Score_Manage.IsAutoSchd=1 and VB_Score_Manage.deptid in('001','002','004','005','006','008','015','018','019','020','021') and VB_SelectCourse_Base.GraduateID is not null '''
    try:
        cursor.execute(sql)
        results_cou = cursor.fetchall()

        # 处理初始数据（转换为带学生的教学计划）
        for cou in results_cou:
            task_found = False
            for task in task_course:
                if task[0] == str(cou[0]):
                    task[6].append(cou[6])
                    task_found = True
                    break
            if not task_found:
                task_course.append(
                    [str(cou[0]), cou[1], cou[2], cou[3], cou[4], cou[5], [cou[6]], cou[7], cou[8], cou[9], cou[10],
                     cou[11]])
    except Exception as e:
        print(e)
    return task_course


def init_sel_students():
    sel_stu_info = []
    if (config.db == 1):
        sql = ''' select distinct GraduateID ,Sno from VB_SelectCourse_Base where year='2021-2022' and term='1' and VB_SelectCourse_Base.GraduateID is not null and Sno in (select VB_Score_Manage.Sno
        from VB_Score_Manage
        where VB_Score_Manage.year='2021-2022' and VB_Score_Manage.term='1' and WeekStart is not null and WeekEnd is not null and 
        VB_Score_Manage.courseid not in('01544003','01524003','01524002','02614002','02614003','00114002','01214002','00414003','00214002','00214003','00514002','00514003','00114002','00414003','01314003','00914001',
        '00914002','00914003','01214001','01314003','01414002','01414001','01414002','01314001','01214003','01414003','00314005','01014003','00314005','00314006','01014001','01014003','00714001',
        '00314006','01114003','01114003','01114002','01114003','02624002','02624001','02624003','00124002','00162001','07024003','07024002','07024001','01024006','01024005','00324007',
        '00324006','05524001','00724004','01124002','01124003','01524301','01424004','01424003','0124002','01424005','01424002','00744001','00744002','00144001','00144003','00344015','00944003',
        '01944319','02141106','01544002','02644008','00614002L','00314002','01324901','00114003','00344902','00344801','00344013','00344012','00344014','01614001','01614002','00614003','00614001','01711410',
         '01714006','00111001') and VB_Score_Manage.deptid='001' ) '''
    if (config.db == 2):
        # sql = ''' select distinct GraduateID ,Sno from VB_SelectCourse_Base where year='2022-2023' and term='2' and VB_SelectCourse_Base.GraduateID is not null and Sno in (select VB_Score_Manage.Sno
        #     from VB_Score_Manage
        #     where VB_Score_Manage.year='2022-2023' and VB_Score_Manage.term='2' and WeekStart is not null and WeekEnd is not null and
        #     VB_Score_Manage.courseid not in('01544003','01524003','01524002','02614002','02614003','00114002','01214002','00414003','00214002','00214003','00514002','00514003','00114002','00414003','01314003','00914001',
        #     '00914002','00914003','01214001','01314003','01414002','01414001','01414002','01314001','01214003','01414003','00314005','01014003','00314005','00314006','01014001','01014003','00714001',
        #     '00314006','01114003','01114003','01114002','01114003','02624002','02624001','02624003','00124002','00162001','07024003','07024002','07024001','01024006','01024005','00324007',
        #     '00324006','05524001','00724004','01124002','01124003','01524301','01424004','01424003','0124002','01424005','01424002','00744001','00744002','00144001','00144003','00344015','00944003',
        #     '01944319','02141106','01544002','02644008','00614002L','00314002','01324901','00114003','00344902','00344801','00344013','00344012','00344014','01614001','01614002','00614003','00614001','01711410',
        #      '01714006','00111001') and VB_Score_Manage.deptid in('001','002','004','005')) '''
        sql = ''' select distinct GraduateID from VB_SelectCourse_Base where year='2022-2023' and term='2' and VB_SelectCourse_Base.GraduateID is not null and Sno in (select VB_Score_Manage.Sno
            from VB_Score_Manage where VB_Score_Manage.year='2022-2023' and VB_Score_Manage.term='2' and WeekStart is not null and WeekEnd is not null and 
            VB_Score_Manage.IsAutoSchd=1 and VB_Score_Manage.deptid in('001','002','004','005','006','008','015','018','019','020','021')) '''
    try:
        cursor.execute(sql)
        results_stu = cursor.fetchall()
        for stu in results_stu:
            sel_stu_info.append(stu)
    except Exception as e:
        print(e)
    return sel_stu_info


def init_sel_teachers():
    sel_tea_info = []
    if (config.db == 1):
        sql = ''' select distinct Teacher, Sno from VB_SelectCourse_Base where year='2021-2022' and term='1' and VB_SelectCourse_Base.GraduateID is not null and Sno in (select VB_Score_Manage.Sno
        from VB_Score_Manage
        where VB_Score_Manage.year='2021-2022' and VB_Score_Manage.term='1' and WeekStart is not null and WeekEnd is not null and 
        VB_Score_Manage.courseid not in('01544003','01524003','01524002','02614002','02614003','00114002','01214002','00414003','00214002','00214003','00514002','00514003','00114002','00414003','01314003','00914001',
        '00914002','00914003','01214001','01314003','01414002','01414001','01414002','01314001','01214003','01414003','00314005','01014003','00314005','00314006','01014001','01014003','00714001',
        '00314006','01114003','01114003','01114002','01114003','02624002','02624001','02624003','00124002','00162001','07024003','07024002','07024001','01024006','01024005','00324007',
        '00324006','05524001','00724004','01124002','01124003','01524301','01424004','01424003','0124002','01424005','01424002','00744001','00744002','00144001','00144003','00344015','00944003',
        '01944319','02141106','01544002','02644008','00614002L','00314002','01324901','00114003','00344902','00344801','00344013','00344012','00344014','01614001','01614002','00614003','00614001','01711410',
         '01714006','00111001') and VB_Score_Manage.deptid='001' ) '''
    if (config.db == 2):
        # sql = ''' select distinct Teacher, Sno from VB_SelectCourse_Base where year='2022-2023' and term='2' and VB_SelectCourse_Base.GraduateID is not null and Sno in (select VB_Score_Manage.Sno
        #         from VB_Score_Manage
        #         where VB_Score_Manage.year='2022-2023' and VB_Score_Manage.term='2' and WeekStart is not null and WeekEnd is not null and
        #         VB_Score_Manage.courseid not in('01544003','01524003','01524002','02614002','02614003','00114002','01214002','00414003','00214002','00214003','00514002','00514003','00114002','00414003','01314003','00914001',
        #         '00914002','00914003','01214001','01314003','01414002','01414001','01414002','01314001','01214003','01414003','00314005','01014003','00314005','00314006','01014001','01014003','00714001',
        #         '00314006','01114003','01114003','01114002','01114003','02624002','02624001','02624003','00124002','00162001','07024003','07024002','07024001','01024006','01024005','00324007',
        #         '00324006','05524001','00724004','01124002','01124003','01524301','01424004','01424003','0124002','01424005','01424002','00744001','00744002','00144001','00144003','00344015','00944003',
        #         '01944319','02141106','01544002','02644008','00614002L','00314002','01324901','00114003','00344902','00344801','00344013','00344012','00344014','01614001','01614002','00614003','00614001','01711410',
        #          '01714006','00111001') and VB_Score_Manage.deptid in('001','002','004','005')) '''
        sql = ''' select distinct Teacher from VB_SelectCourse_Base where year='2022-2023' and term='2' and VB_SelectCourse_Base.GraduateID is not null and Sno in (select VB_Score_Manage.Sno
                from VB_Score_Manage
                where VB_Score_Manage.year='2022-2023' and VB_Score_Manage.term='2' and WeekStart is not null and WeekEnd is not null and 
                VB_Score_Manage.IsAutoSchd=1 and VB_Score_Manage.deptid in('001','002','004','005','006','008','015','018','019','020','021')) '''
    try:
        cursor.execute(sql)
        results_tea = cursor.fetchall()
        for tea in results_tea:
            sel_tea_info.append(tea)
    except Exception as e:
        print(e)
    return sel_tea_info


def init_classrooms():
    classroom_info = []
    # sql = "select ClassRoomID,PeopleNumber from VB_ClassRoom where ClassTypeName in ( '普通教室')"
    # 查询可用教室（之前是所有教室都可用）
    sql = '''SELECT ClassRoomID,PeopleNumber,BuildingID FROM VB_ClassRoom WHERE ClassRoomID IN(SELECT DISTINCT ClassRoomID FROM nTB_ArrClassNew_OpenClsRoom)
                AND ClassTypeName in ('普通教室') '''
    try:
        cursor.execute(sql)
        results_clr = cursor.fetchall()
        for clr in results_clr:
            classroom_info.append(clr)  # 将教室信息添加到classroom_info列表中
            # classroom_info = [[26，150][74,40],[2,80].......]
    except Exception as e:
        print(e)
    return classroom_info


# 教学楼信息
def init_building():
    building_info = []
    sql = '''SELECT BuildingID FROM dbo.nTB_Building_info WHERE District = 0'''
    try:
        cursor.execute(sql)
        result_clr = cursor.fetchall()
        for clr in result_clr:
            building_info.append(clr)
    except Exception as e:
        print(e)
    return building_info


# 教学楼距离表
def init_buildingDistance():
    buildingDistance_info = []
    sql = '''SELECT * FROM dbo.nTB_Buildings_Distance'''
    try:
        cursor.execute(sql)
        result_clr = cursor.fetchall()
        for clr in result_clr:
            buildingDistance_info.append(clr)
    except Exception as e:
        print(e)
    return buildingDistance_info


# 导入数据到数据库中
def InsertBatchToDB(result):
    # del_sql = "delete from dbo.nTB_ArrClassPreNewAutoSd_info"
    del_sql = "TRUNCATE TABLE dbo.nTB_ArrClassPreNewAutoSd_info"
    int_sql = "insert into dbo.nTB_ArrClassPreNewAutoSd_info values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    # sql = "insert into dbo.testInsert values ('wang',12,'男')"
    # sql = "insert into dbo.testInsert values (%s,%s,%s)"
    try:
        cursor.execute(del_sql)
        cursor.executemany(int_sql, result)
        db.commit()
    except Exception as e:
        print(e)
        db.rollback()
    finally:
        db.close()
